﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_Clearance : UserControl
    {
        //private int focusIndex;
        public panel_Clearance()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            //focusIndex = 0;
            LoadData();            
            this.progressPanel1.Visible = false;
            this.CustomClerance.DoubleClick += CustomerClerance_DoubleClick;
            this.CustomClerance.KeyPress += CustomerClerance_KeyPress;
            this.CustomClerance.GotFocus += CustomerClerance_GotFocus;

            this.CustomClerance.PopupMenuShowing += CustomerClerance_PopupMenuShowing;
            this.clearanceContext.ItemClicked += clearanceContext_ItemClicked;
        }

        void clearanceContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.clearanceContext.Hide();
            switch (e.ClickedItem.Name)
            {
                case "editSelection":
                    callEditForm();
                    break;
                case "addSelection":
                    frm_Add_CustomClearance add_Form = new frm_Add_CustomClearance();
                    //add_Form.Show();
                    add_Form.ShowDialog();
                    LoadData();
                    break;
                case "importSelection":
                    importData();
                    break;
                case "deleteSelection":
                    deleteData();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void CustomerClerance_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.clearanceContext.Show(MousePosition);
        }

        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();            
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            sourceDataSet = mySQLManagement.SQL_Select("[241_CustomClearance]", "");
            if (sourceDataSet != null)
            {
                gridCustomClearance.DataSource = sourceDataSet.Tables[0];
            }
            else
            {
                MessageBox.Show("There is no data for Custom Clearance");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(string));
                newTable.Columns.Add("RegisteredDate", typeof(DateTime));
                newTable.Columns.Add("FinishedDate", typeof(DateTime));
                newTable.Columns.Add("Note", typeof(string));
                gridCustomClearance.DataSource = newTable;
            }
            CustomClerance.Columns[0].Caption = "Custom Clerance ID";
            CustomClerance.Columns[1].Caption = "Registered Date";
            CustomClerance.Columns[2].Caption = "Finished Date";
            CustomClerance.Columns[3].Caption = "Note";
            //CustomClerance.SelectRow(focusIndex);
            //CustomClerance.FocusedRowHandle = focusIndex;
            CustomClerance.BestFitColumns();
        }

        private void callEditForm()
        {
            DataRow row = CustomClerance.GetFocusedDataRow();
            //focusIndex = CustomClerance.GetFocusedDataSourceRowIndex();
            frm_Edit_CustomClearance editForm = new frm_Edit_CustomClearance();
            bool loadData = editForm.LoadData(row[0].ToString());
            if (loadData)
            {
                //editForm.Show();
                editForm.ShowDialog();
                LoadData();
            }
            else
            {
                editForm.Dispose();
                MessageBox.Show("Cannot get data for selected record");
            }
        }

        private void CustomerClerance_DoubleClick(object sender, System.EventArgs e)
        {
            callEditForm();
        }

        private void CustomerClerance_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)Keys.Return)
            {
                callEditForm();
            }
        }

        private void CustomerClerance_GotFocus(object sender, System.EventArgs e)
        {
            LoadData();
        }

        private void cleranceUpdate_Click(object sender, EventArgs e)
        {
            callEditForm();
        }

        private void cleranceAdd_Click(object sender, EventArgs e)
        {            
            frm_Add_CustomClearance addForm = new frm_Add_CustomClearance();
            //addForm.Show();
            addForm.ShowDialog();
            LoadData();
        }

        private void cleranceImport_Click(object sender, EventArgs e)
        {
            importData();
        }

        private void importData()
        {
            importFile.Filter = "Excel 97-2003 (.xls)|*.xls|Excel 2007-2010 (.xlsx)|*.xlsx";
            importFile.FilterIndex = 1;
            importFile.Multiselect = false;            
            DialogResult userClickOK = importFile.ShowDialog();
            if (userClickOK == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    Control parent = progressPanel1.Parent;
                    progressPanel1.Location = new Point(parent.Bounds.X + parent.Bounds.Width / 2 - progressPanel1.Width / 2, parent.Bounds.Y + parent.Bounds.Height / 2 - progressPanel1.Height / 2);
                    progressPanel1.Visible = true;
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        importFile.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd = new OleDbCommand("Select [ID],[RegisterDate],[FinishedDate],[Note] from [Sheet1$]", excelConnection);
                    excelConnection.Open();
                    OleDbDataReader dReader;
                    dReader = cmd.ExecuteReader();
                    int i = 0;
                    myConnection.Open();
                    while (dReader.Read())
                    {
                        bool exist = false;
                        try
                        {
                            SqlCommand cmd_sql = myConnection.CreateCommand();
                            cmd_sql.CommandText = "SELECT * FROM \"241_CustomClearance\" WHERE ID='" + dReader["ID"].ToString() + "'";
                            SqlDataReader dataReader = cmd_sql.ExecuteReader();
                            if (dataReader.Read())
                            {
                                exist = true;
                            }
                            dataReader.Close();
                            cmd_sql.Dispose();
                        }
                        catch
                        {
                            exist = true;
                        } 

                        if (!exist)
                        {
                            DateTime register;
                            DateTime.TryParse(dReader["RegisterDate"].ToString(), out register);
                            DateTime finish;
                            DateTime.TryParse(dReader["FinishedDate"].ToString(), out finish);
                            string sql = "INSERT INTO \"241_CustomClearance\" "
                                    + "(ID,RegisterDate,FinishedDate,Note) "
                                    + "VALUES ('" + dReader["ID"].ToString() + "',@register,@finished,'" + dReader["Note"].ToString() + "')";
                            SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                            cmd_sql.Parameters.Add("@register", SqlDbType.DateTime).Value = register.Date;
                            cmd_sql.Parameters.Add("@finished", SqlDbType.DateTime).Value = finish.Date;
                            cmd_sql.ExecuteNonQuery();
                            cmd_sql.Dispose();
                            i++;                            
                        }
                    }
                    myConnection.Close();
                    progressPanel1.Visible = false;
                    excelConnection.Close();
                    MessageBox.Show(i + " recoreds have been imported.");
                    LoadData();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void cleranceDelete_Click(object sender, EventArgs e)
        {
            deleteData();
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void deleteData()
        {
            if (MessageBox.Show("Are you really to delete selected record now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                //focusIndex = CustomClerance.GetFocusedDataSourceRowIndex();
                int[] selected_row = CustomClerance.GetSelectedRows();
                myConnection.Open();
                int k = 0;
                foreach (int i in selected_row)
                {
                    bool exist = false;
                    try
                    {
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT * FROM [241_CustomClearance] WHERE"
                            + " ID='" + CustomClerance.GetRowCellValue(i, "ID").ToString() + "'";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            exist = true;
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                    }
                    catch
                    {
                        exist = false;
                    }
                    if (exist)
                    {
                        try
                        {
                            string sql = "DELETE FROM [241_CustomClearance] "
                                            + "WHERE ID='" + CustomClerance.GetRowCellValue(i, "ID").ToString() + "'";
                            SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                            cmd_sql.ExecuteNonQuery();
                            cmd_sql.Dispose();
                            k++;
                        }
                        catch
                        {

                        }
                    }
                }
                myConnection.Close();
                MessageBox.Show(k + " recoreds have been deleted.");
                LoadData();
            }
        }
    }
}
